var express = require('express');
const myroute = express.Router()
const mySql = require('../model/index')

//待办事项-获取数据
myroute.get('/todo/getone', (req, res) => {
    const sql = ` SELECT * FROM admin RIGHT JOIN application ON admin.work_id=application.apply_name LEFT 
    JOIN postadmin ON admin.jobs=postadmin.post_id LEFT JOIN department ON department.id=postadmin.post_department 
    LEFT JOIN application_type ON application_type.id=application.type WHERE state='${req.query.mytype}' AND superior='${req.query.jobs}'
     LIMIT 0, 1000; `
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
myroute.get('/todo/gettwo', (req, res) => {
    const sql = ` SELECT * FROM admin RIGHT JOIN meeting ON admin.work_id=meeting.meeting_per LEFT JOIN postadmin ON admin.jobs=postadmin.post_id LEFT JOIN meetingroom ON meeting.meeting_room=meetingroom.metroom_id LEFT JOIN department ON department.id=postadmin.post_department WHERE meeting_state='${req.query.mytype}' AND superior='${req.query.jobs}'  LIMIT 0, 1000; `
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//待办事项-修改
myroute.get('/todo/changeone', (req, res) => {
    let x = req.query.time.replace('T', ' ')
    x = x.slice(0, x.length - 1)
    const sql = `UPDATE application SET state='已审核' , approvaltime='${x}' WHERE mun='${req.query.mun}'`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
myroute.get('/todo/changetwo', (req, res) => {
    let x = req.query.time.replace('T', ' ')
    x = x.slice(0, x.length - 1)
    const sql = `UPDATE meeting SET meeting_state='已审核' , meeting_agreetime='${x}' WHERE meeting_id='${req.query.id}'`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//我的申请-获取数据
myroute.get('/myapply/getone', (req, res) => {
    const sql = ` SELECT * FROM admin RIGHT JOIN application ON admin.work_id=application.apply_name LEFT 
    JOIN postadmin ON admin.jobs=postadmin.post_id  LEFT JOIN department ON department.id=postadmin.post_department 
    LEFT JOIN application_type ON application_type.id=application.type WHERE state='${req.query.mystate}' AND work_id=${req.query.workid} LIMIT 0, 1000`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            if (data.length != 0) {
                const sql2 = `SELECT NAME FROM admin RIGHT JOIN postadmin ON admin.jobs=postadmin.post_id WHERE post_id=${data[0].superior}`
                mySql.query(sql2, (err2, data2) => {
                    if (err2) {
                        console.log(err2)
                        res.send({ err2: 1, msg: err2 })
                    } else {
                        res.send({ msg: '成功', data: data, data2: data2 })
                    }
                })
            }
        }
    })
})
myroute.get('/myapply/gettwo', (req, res) => {
    const sql = `SELECT * FROM admin RIGHT JOIN meeting ON admin.work_id=meeting.meeting_per LEFT JOIN postadmin ON admin.jobs=postadmin.post_id 
    LEFT JOIN meetingroom ON meeting.meeting_room=meetingroom.metroom_id LEFT JOIN department ON department.id=postadmin.post_department 
    WHERE meeting_state='${req.query.mystate}' AND work_id=${req.query.workid} LIMIT 0, 1000`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            if (data.length != 0) {
                const sql2 = `SELECT NAME FROM admin RIGHT JOIN postadmin ON admin.jobs=postadmin.post_id WHERE post_id=${data[0].superior}`
                mySql.query(sql2, (err2, data2) => {
                    if (err2) {
                        console.log(err2)
                        res.send({ err2: 1, msg: err2 })
                    } else {
                        res.send({ msg: '成功', data: data, data2: data2 })
                    }
                })
            }
        }
    })
})
//打卡申请
myroute.get('/clockin/get', (req, res) => {
    const sql = ` SELECT * FROM admin RIGHT JOIN clock ON admin.work_id=clock.word_id WHERE work_id=${req.query.myid}`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//会议获取数据
myroute.get('/meeting/get', (req, res) => {
    const sql = `SELECT * FROM admin RIGHT JOIN meeting ON admin.work_id=meeting.meeting_per LEFT JOIN postadmin ON admin.jobs=postadmin.post_id 
    LEFT JOIN meetingroom ON meeting.meeting_room=meetingroom.metroom_id LEFT JOIN department ON department.id=postadmin.post_department 
    WHERE post_department=${req.query.department} LIMIT 0, 1000`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//发起会议-数据显示
myroute.get('/meeting/addget', (req, res) => {
    console.log(req.query)
    let x = parseInt(Math.random() * (999999 - 100000) + 100000)
    const sql1 = `SELECT * FROM admin  LEFT JOIN postadmin ON admin.jobs=postadmin.post_id LEFT JOIN department ON department.id=postadmin.post_department WHERE work_id=${req.query.workid}`
    const sql2 = `SELECT * FROM meeting WHERE meeting_num=${x}`
    mySql.query(sql2, (err2, data2) => {
        if (err2) {
            console.log(err2)
            res.send({ err: 1, msg: err2 })
        } else {
            mySql.query(sql1, (err, data) => {
                if (err) {
                    console.log(err)
                    res.send({ err: 1, msg: err })
                } else {
                    if (data2.length == 0) {
                        res.send({ err: 1, data: data, data2: x })
                    }
                    else {
                        res.send({ data: data, data2: parseInt(Math.random() * (999999 - 100000) + 100000) })
                    }
                }
            })

        }
    })

})
//发起会议
myroute.post('/meeting/add', (req, res) => {
    let mydata = req.body
    const sql = `INSERT INTO meeting VALUES(NULL,${mydata.meeting_num},${mydata.meeting_room},'${mydata.meeting_tit}','${mydata.meeting_startime}','${mydata.meeting_endtime}',${mydata.work_id},'${mydata.meeting_todotime}','${mydata.meeting_cont}',default,null,${mydata.meeting_pernum},null) `
    const sql2 = `INSERT INTO message VALUES(NULL,'${mydata.name}','${mydata.name}于${mydata.meeting_todotime}发起一条会议申请，请您处理','${mydata.meeting_todotime}',default,2,${mydata.meeting_num})`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            mySql.query(sql2, (err2, data2) => {
                if (err2) {
                    console.log(err2)
                    res.send({ err: 1, msg: err2 })
                } else {
                    res.send({ msg: '成功' })
                }
            })
        }
    })
})
//绩效获取数据
myroute.get('/performance/get', (req, res) => {
    const sql = `SELECT * FROM admin RIGHT JOIN performance ON performance.reviewer=admin.work_id LEFT JOIN postadmin ON admin.jobs=postadmin.post_id LEFT JOIN department ON department.id=postadmin.post_department WHERE superior=${req.query.mysuper}`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//绩效填写
myroute.get('/performance/change', (req, res) => {
    let x = req.query.mytime.replace('T', ' ')
    x = x.slice(0, x.length - 1)
    const sql = `UPDATE performance SET audit_time='${x}' , link='已完成',score='${req.query.score}' WHERE per_id=${req.query.perid}`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功' })
        }
    })
})
//我的消息获取数据
myroute.get('/mynews/get', (req, res) => {
    const sql = `SELECT * FROM message WHERE originator!='${req.query.myname}'`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
module.exports = myroute
//我的消息修改已读
myroute.get('/mynews/edit', (req, res) => {
    let x = ''
    let y = ''
    for (let i = 0; i < req.query.myid.length; i++) {
        x += req.query.myid[i] + ','
        y += `WHEN ${req.query.myid[i]} THEN '已读' `
    }
    x = x.slice(0, x.length - 1)
    const sql = `  UPDATE message SET TYPE = CASE id  ${y} END WHERE id IN (${x})`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功' })
        }
    })
})
//我的消息全部修改为已读
myroute.get('/mynews/allread', (req, res) => {
    const sql = `UPDATE message SET TYPE='已读'`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功' })
        }
    })
})
//我的消息删除
myroute.get('/mynews/del', (req, res) => {
    let x = ''
    for (let i = 0; i < req.query.myid.length; i++) {
        x += req.query.myid[i] + ','
    }
    x = x.slice(0, x.length - 1)
    const sql = `DELETE FROM message WHERE id IN (${x})`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功' })
        }
    })
})
//岗位查询
myroute.get('/post/get', (req, res) => {
    const sql = `SELECT * FROM postadmin LEFT JOIN department ON post_department=id WHERE post_id="${req.query.postname}"`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//待办事项-修改
//请假
myroute.get('/todo/change1', (req, res) => {
    const sql = `UPDATE application SET state='已审核' , approvaltime='${req.query.time}',agree='${req.query.agree}' WHERE mun='${req.query.mun}'`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//会议
myroute.get('/todo/change2', (req, res) => {
    const sql = `UPDATE meeting SET meeting_state='已审核' , meeting_agreetime='${req.query.time}',agree='${req.query.agree}' WHERE meeting_id='${req.query.id}'`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//调薪
myroute.get('/todo/changeSalary', (req, res) => {
    const sql = `UPDATE admin SET money='${req.query.money}' WHERE work_id='${req.query.id}'`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//转岗
myroute.get('/todo/changeJob', (req, res) => {
    const sql = `UPDATE admin SET jobs='${req.query.jobs}' WHERE work_id='${req.query.id}'`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            res.send({ msg: '成功', data: data })
        }
    })
})
//发起转岗-数据显示
myroute.get('/salary/addget', (req, res) => {
    let x = parseInt(Math.random() * (9999 - 1000) + 1000)
    const sql = `SELECT * FROM application WHERE mun=${x}`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err2 })
        } else {
            if (data.length != 0) {
                res.send({ data: x })
            } else {
                // console.log(x);
                res.send({ data: parseInt(Math.random() * (9999 - 1000) + 1000) })
            }
        }
    })

})
//发起转岗
myroute.post('/salary/add', (req, res) => {
    let mydata = req.body
    const sql = `INSERT INTO application VALUES(NULL,'LS${mydata.mun}',6,default,${mydata.applyper},null,'${mydata.con}',null,'${mydata.applytime}',null,${mydata.applyper},null,${mydata.newmoney},null,null) `
    const sql2 = `INSERT INTO message VALUES(NULL,'${mydata.name}','${mydata.name}于${mydata.applytime}发起一条会议申请，请您处理','${mydata.applytime}',default,2,'LS${mydata.mun}')`
    mySql.query(sql, (err, data) => {
        if (err) {
            console.log(err)
            res.send({ err: 1, msg: err })
        } else {
            mySql.query(sql2, (err2, data2) => {
                if (err2) {
                    console.log(err2)
                    res.send({ err: 1, msg: err2 })
                } else {
                    res.send({ msg: '成功' })
                }
            })
        }
    })
})
module.exports = myroute